/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO.MySQL;

import Biblioteca.Library;
import Biblioteca.VerificacaoErros;
import ClassesUnicas.Profissional;
import DAO.DAOProfissional;
import InstanciaJogo.InstanciaJogoSwing;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 * DAOProfissionalMySQL
 * 
 * Versao 1.0
 * 
 * Data Criação 13/03/2014
 *
 * @author Igor
 */
public class DAOProfissionalMySQL extends DAOConexaoMySQL implements DAOProfissional {
    //Outros metodos
    @Override
    public long autenticarProfissional(String versao, String email, String senha) {
        long vReturn = -1; //Representa que nao achou o profissional
        try {
            CallableStatement cs;
            cs = this.con.getCon().prepareCall("{ ? = call AUTENTICAR_PROFISSIONAL(?,?,?)}");

            cs.registerOutParameter(1, Types.NUMERIC);
            cs.setString(2, versao);
            cs.setString(3, email);
            cs.setString(4, senha);
            cs.execute();

            vReturn = cs.getLong(1);
            this.con.getStm().executeBatch();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        return vReturn;
    }

    @Override
    public Profissional buscaProfissional(String email, String senha) {
        return buscaProfissional(String.valueOf(autenticarProfissional(Library.getVersaoTREF(), email, senha)));
    }

    @Override
    public Profissional buscaProfissional(String idProfissional) {
        ResultSet result = null;
        Profissional profissional = null;
        try {
            result = this.select.executeQuery("select\n" +
                                            "a.ID_PROFISSIONAL,\n" +
                                            "a.NOME,\n" +
                                            "a.EMAIL,\n" +
                                            "AES_DECRYPT(a.SENHA, 'SENHA') as SENHA\n"+
                                            "from profissional a\n" +
                                            "where a.ID_PROFISSIONAL = "+idProfissional);

            while (result.next()) {
                profissional = new Profissional();

                profissional.setIdProfissional(result.getLong("ID_PROFISSIONAL"));
                profissional.setNome(result.getString("NOME"));
                profissional.setEmail(result.getString("EMAIL"));
                profissional.setSenha(result.getString("SENHA"));

            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        
        return profissional;
    }
    
    @Override
    public ArrayList<Profissional> buscaProfissionais(String idProfissional, String nome) {
        ResultSet result = null;
        ArrayList<Profissional> profissionais = new ArrayList<Profissional>();
        String clausulaWhere = "";

        //Cria clausula where
        if(idProfissional != null){
            if(clausulaWhere.isEmpty()){
                clausulaWhere = "where ";
            }else{
                clausulaWhere = clausulaWhere + " and ";
            }
            clausulaWhere = clausulaWhere + " a.ID_PROFISSIONAL = "+idProfissional;
        }

        if(nome != null){
            if(clausulaWhere.isEmpty()){
                clausulaWhere = "where ";
            }else{
                clausulaWhere = clausulaWhere + " and ";
            }
            clausulaWhere = clausulaWhere + " a.NOME like '%"+nome+"%'";
        }

        try {
            result = this.select.executeQuery("select\n" +
                                            "a.ID_PROFISSIONAL,\n" +
                                            "a.NOME,\n" +
                                            "a.EMAIL,\n" +
                                            "AES_DECRYPT(a.SENHA, 'SENHA') as SENHA\n"+
                                            "from profissional a\n" +
                                            clausulaWhere);

            while (result.next()) {
                Profissional profissional = new Profissional();

                profissional.setIdProfissional(result.getLong("ID_PROFISSIONAL"));
                profissional.setNome(result.getString("NOME"));
                profissional.setEmail(result.getString("EMAIL"));
                profissional.setSenha(result.getString("SENHA"));
                profissionais.add(profissional);

            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        
        return profissionais;
    }

    @Override
    public String gravaProfissional(Profissional profissional) {
        String vReturn = ""; //Representa que nao achou o profissional
        try {
            CallableStatement cs;
            cs = this.con.getCon().prepareCall("{ ? = call GRAVA_PROFISSIONAL(?,?,?,?)}");

            cs.registerOutParameter(1, Types.NUMERIC);
            cs.setLong(2, profissional.getIdProfissional());
            cs.setString(3, profissional.getNome());
            cs.setString(4, profissional.getEmail());
            cs.setString(5, profissional.getSenha());
            cs.execute();

            vReturn = cs.getString(1);
            this.con.getStm().executeBatch();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, VerificacaoErros.retornaMensagemErro(e), "Erro!", JOptionPane.ERROR_MESSAGE);
        }
        return vReturn;
    }
}
